class ArticleDao {
  constructor(pool) {
    this.pool = pool;
  }

  // 获取收购公告列表（根据当前时间过滤过期公告）
  async getArticles(page = 1, pageSize = 10, userId) {
    // 校验并处理page和pageSize参数
    page = Math.max(1, parseInt(page)); // 默认从第1页开始，防止负值或0
    pageSize = Math.max(1, parseInt(pageSize)); // 默认每页至少1条，防止负值或0

    const offset = (page - 1) * pageSize;

    // 获取分页数据
    const articlesSql = `
    SELECT a.*, u.*
    FROM articles a
    JOIN users u ON a.user_id = u.user_id
    WHERE a.user_id = ${userId} 
    ORDER BY a.published_at DESC
    LIMIT ${pageSize} OFFSET ${offset}
  `;

    try {
      // 执行获取分页数据的 SQL 查询
      const [articles] = await this.pool.execute(articlesSql);

      // 格式化结果，直接返回前端所需的数据格式
      const formattedArticles = articles.map(article => ({
      article_id: article.article_id,
      title: article.title,
      content: article.content,
      product_name: article.product_name,
      product_category: article.product_category,
      price_offer: article.price_offer,
      total_quantity: article.total_quantity,
      remaining_quantity: article.remaining_quantity,
      region: article.region,
      published_at: article.published_at,
      deadline: article.deadline,
      publisher: {
        user_id: article.user_id,
        user_name: article.nick_name,
        role: article.role_name,
        phone: article.user_phone,
        email: article.user_email,
        address: article.user_address
        }
      }));
      return {
        articles:formattedArticles, // 直接返回文章数据
      };
    } catch (error) {
      throw new Error('获取公告时发生错误: ' + error.message);
    }
  }

  // 获取全部收购公告（过滤过期）
  async getAllArticles() {
    const sql = `
      SELECT a.*, u.*
      FROM articles a
      JOIN users u ON a.user_id = u.user_id
      WHERE a.deadline IS NULL OR a.deadline > NOW()
      ORDER BY a.published_at DESC
    `;
    try {
      const [articles] = await this.pool.execute(sql);

      const formattedArticles = articles.map(this.formatArticle);

      return formattedArticles;
    } catch (error) {
      console.error('Error fetching all articles:', error);
      throw new Error('获取全部公告时发生错误: ' + error.message);
    }
  }


  // 创建收购公告
  async createArticle(title, content, product_name, product_category, price_offer, total_quantity, remaining_quantity, region, deadline, userId) {
    const sql = `
      INSERT INTO articles (user_id, title, content, product_name, product_category, price_offer, total_quantity, remaining_quantity, region, deadline)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
    try {
      const [result] = await this.pool.execute(sql, [userId, title, content, product_name, product_category, price_offer, total_quantity, remaining_quantity, region, deadline]);

      return null;
    } catch (error) {
      console.error('Error creating article:', error);
      throw new Error('创建公告时发生错误: ' + error.message);
    }
  }

  // 修改收购公告（延长和截止都公用这个）
  async updateArticle(article_id, deadline, userId) {
    const updateSql = `
      UPDATE articles
      SET deadline = ?
      WHERE article_id = ? AND user_id = ?
    `;
    const selectSql = `
      SELECT a.*, u.*
      FROM articles a
      JOIN users u ON a.user_id = u.user_id
      WHERE a.article_id = ?
    `;
    try {
      const [result] = await this.pool.execute(updateSql, [deadline, article_id, userId]);

      if (result.affectedRows === 0) {
        throw new Error('修改失败或权限不足');
      }

      // 修改成功后查询完整数据
      const [rows] = await this.pool.execute(selectSql, [article_id]);
      if (rows.length === 0) {
        throw new Error('公告不存在');
      }

      const article = rows[0];
      return {
        article_id: article.article_id,
        title: article.title,
        content: article.content,
        product_name: article.product_name,
        product_category: article.product_category,
        price_offer: article.price_offer,
        total_quantity: article.total_quantity,
        remaining_quantity: article.remaining_quantity,
        region: article.region,
        published_at: article.published_at,
        deadline: article.deadline,
        publisher: {
          user_id: article.user_id,
          user_name: article.nick_name,
          role: article.role_name,
          phone: article.user_phone,
          email: article.user_email,
          address: article.user_address
        }
      };
    } catch (error) {
      console.error('Error updating article:', error);
      throw new Error('修改公告时发生错误: ' + error.message);
    }
  }
  //获取剩余数量
  async getRemainingQuantity(article_id) {
    const sql = `SELECT remaining_quantity FROM articles WHERE article_id = ?`;
    const [rows] = await this.pool.execute(sql, [article_id]);
    if (rows.length === 0) throw new Error('公告不存在');
    return rows[0].remaining_quantity;
  }
  //减少剩余数量
  async reduceRemainingQuantity(article_id, quantity) {
    const sql = `UPDATE articles SET remaining_quantity = remaining_quantity - ? WHERE article_id = ? AND remaining_quantity >= ?`;
    const [result] = await this.pool.execute(sql, [quantity, article_id, quantity]);
    if (result.affectedRows === 0) throw new Error('剩余数量不足');
  }
  // 获取经销商发布的收购公告
  async getDealerArticles() {
    const sql = `
      SELECT a.*, u.*
      FROM articles a
      JOIN users u ON a.user_id = u.user_id
      WHERE u.role_name = 'dealer' AND (a.deadline IS NULL OR a.deadline > NOW())
      ORDER BY a.published_at DESC
    `;
    try {
      const [articles] = await this.pool.execute(sql);
      return articles.map(this.formatArticle);
    } catch (error) {
      console.error('获取经销商公告失败:', error);
      throw new Error('获取经销商公告时发生错误: ' + error.message);
    }
  }

  // 获取政府发布的收购公告
  async getGovArticles() {
    const sql = `
      SELECT a.*, u.*
      FROM articles a
      JOIN users u ON a.user_id = u.user_id
      WHERE u.role_name = 'gov' AND (a.deadline IS NULL OR a.deadline > NOW())
      ORDER BY a.published_at DESC
    `;
    try {
      const [articles] = await this.pool.execute(sql);
      return articles.map(this.formatArticle);
    } catch (error) {
      console.error('获取政府公告失败:', error);
      throw new Error('获取政府公告时发生错误: ' + error.message);
    }
  }
  async searchArticles(title, role) {
    const sql = `
        SELECT a.*, u.*
        FROM articles a
        JOIN users u ON a.user_id = u.user_id
        WHERE a.title LIKE ?
        AND u.role_name = ?
        AND (a.deadline IS NULL OR a.deadline > NOW())
        ORDER BY a.published_at DESC
    `;

    try {
        const [articles] = await this.pool.execute(sql, [`%${title}%`, role]);
        return articles.map(this.formatArticle);
    } catch (error) {
        console.error('搜索公告失败:', error);
        throw new Error('数据库查询失败: ' + error.message);
    }
  }

  // 统一格式化公告数据的方法
  formatArticle(article) {
    return {
      article_id: article.article_id,
      title: article.title,
      content: article.content,
      product_name: article.product_name,
      product_category: article.product_category,
      price_offer: article.price_offer,
      total_quantity: article.total_quantity,
      remaining_quantity: article.remaining_quantity,
      region: article.region,
      published_at: article.published_at,
      deadline: article.deadline,
      publisher: {
        user_id: article.user_id,
        user_name: article.nick_name,
        role: article.role_name,
        phone: article.user_phone,
        email: article.user_email,
        address: article.user_address
      }
    };
  }

  // 通过ID获取单个公告
  async getArticleById(article_id) {
    const sql = `
      SELECT a.*, u.*
      FROM articles a
      JOIN users u ON a.user_id = u.user_id
      WHERE a.article_id = ?
    `;
    try {
      const [rows] = await this.pool.execute(sql, [article_id]);
      if (rows.length === 0) {
        throw new Error('公告不存在');
      }
      
      return this.formatArticle(rows[0]);
    } catch (error) {
      console.error('获取公告详情失败:', error);
      throw new Error('获取公告详情时发生错误: ' + error.message);
    }
  }

}

module.exports = ArticleDao;


